import os
import sqlite3
from app.utils import File
from app.utils import System

class SQLiteDB:
    def __init__(self, db_file):
        self.conn = sqlite3.connect(db_file)
        self.cursor = self.conn.cursor()

    def create_table(self, table_name, columns):
        query = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})"
        self.cursor.execute(query)
        return self

    def insert_into(self, table_name, values):
        query = f"INSERT INTO {table_name} VALUES ({values})"
        self.cursor.execute(query)
        return self

    def select_all(self, table_name):
        query = f"SELECT * FROM {table_name}"
        self.cursor.execute(query)
        return self.cursor.fetchall()

    def delete_from(self, table_name, condition):
        query = f"DELETE FROM {table_name} WHERE {condition}"
        self.cursor.execute(query)
        return self

    def commit(self):
        self.conn.commit()

    def close(self):
        self.cursor.close()
        self.conn.close()

# 示例用法
db = SQLiteDB(os.getcwd()+"/../../"+"./data/db/db.db")
File.SetAuthority(os.getcwd()+"/../../"+"./data/db/db.db", System.get_system_name())
# 创建表
db.create_table("students", "id INTEGER PRIMARY KEY, name TEXT, age INTEGER")

# 插入数据
db.insert_into("students", "1, 'John Doe', 20").commit()

# 查询数据
result = db.select_all("students")
print(result)

# 删除数据
db.delete_from("students", "age > 18").commit()

# 关闭数据库连接
db.close()
